from fit_py_ask_number_demo.utils import convert_str_to_json
from fit_py_ask_number_demo.data_managers.query_model_manager import QueryModelManager


class SqlConstructorTemplate:
    def __init__(self):
        self.dsl_json = None
        self.query_type = None

    def dsl2sql(self, dsl, query_type):
        self.get_dsl_info(dsl, query_type)
        sql_template = self.get_template(query_type)
        sql = self.replace_submodule(sql_template)
        final_sql = self.generate_sql(sql)
        return final_sql

    def get_dsl_info(self, dsl, query_type):
        self.dsl_json = dsl
        self.query_type = query_type

    def get_template(self, query_type):
        pass

    def replace_submodule(self, sql_template):
        pass

    def generate_sql(self, sql):
        pass


class BasicSqlConstructor(SqlConstructorTemplate):
    def get_template(self, query_type):
        sql_template = QueryModelManager().get_sql_template_by_query_type(query_type)
        return sql_template

    def replace_submodule(self, sql_template):
        select = self.get_select()
        conditions = self.get_conditions()
        sql = sql_template.replace("{target_field}", select).replace("{conditions}", conditions)
        return sql

    def get_select(self):
        target_fields = self.dsl_json.get("target_field")
        target_names = self.dsl_json.get("target_name")
        select_parts = []
        for field, alias in zip(target_fields, target_names):
            if field == "income":
                field = f"sum({field})"
            select_parts.append(f"{field} as \"{alias}\"")
        select_statement = ", ".join(select_parts)

        if self.dsl_json.get("group_field"):
            group_fields = self.dsl_json["group_field"]
            group_names = self.dsl_json["group_name"]
            group_parts = [f"{field} as \"{alias}\"" for field, alias in zip(group_fields, group_names)]
            group_statement = ", ".join(group_parts)
            select_statement = group_statement + ", " + select_statement

        return select_statement

    def get_conditions(self):
        conditions = ""
        for field, condition in self.dsl_json["conditions"].items():
            field_condition = ""
            for key, value in condition.items():
                value = str(value).replace('[', '(').replace(']', ')')
                field_condition = f"{field} {key} {value} and "
            conditions += field_condition
        conditions = conditions[:-4]
        return conditions

    def generate_sql(self, sql):
        target_field = self.dsl_json.get("target_field")
        if self.dsl_json.get("group_field"):
            group_field = ", ".join(self.dsl_json["group_field"])
            sql = sql.replace("{group_field}", group_field)

        if target_field:
            order_field = target_field[-1] + " " + self.dsl_json.get("order_dir", "desc")
            sql = sql.replace("{order_field}", order_field)

        if self.dsl_json.get("filter_condition"):
            filter_field = list(self.dsl_json["filter_condition"].keys())[0]
            filter_condition = list(self.dsl_json["filter_condition"].values())[0]
            filter_key = self.replace_filter_key(list(filter_condition.keys())[0])
            filter_value = list(filter_condition.values())[0]
            filter_condition_str = f"{filter_field} {filter_key} {filter_value}"
            sql = sql.replace("{filter_condition}", filter_condition_str)

        sql = sql.replace("{limit}", str(self.dsl_json.get("limit", 1000)))

        return sql

    @staticmethod
    def replace_filter_key(key):
        filter_key_dict = {
            "gt": ">",
            "lt": "<"
        }
        return filter_key_dict.get(key, "=")


def dsl2sql_impl(dsl, query_type):
    dsl = convert_str_to_json(dsl)
    sql = BasicSqlConstructor().dsl2sql(dsl, query_type)
    return sql


if __name__ == "__main__":
    dsl = """{"target_field": ["income"],
            "target_name": ["收入"],
            "group_field": ["province"],
            "group_name": ["省份"],
            "order_dir": "asc",
            "limit": 3,
            "conditions": {
                "year": {"in": [2024]},
                "month": {"in": [10, 11, 12]},
                "product_lv1": {"in": ["产互KPI"]}
            }
        }
    """
    query_type = "rank"
    sql = dsl2sql_impl(dsl, query_type)
    print(sql)
